<?php
namespace app\admin\controller;
use cmf\controller\AdminBaseController;
use think\Db;
use app\admin\model\NgeAgentModel;
use app\admin\model\NgeAdvertisingaccountModel;
use think\Loader;
use think\Request;
use think\paginator\driver\Bootstrap;

class StripeController extends AdminBaseController
{

    public function _initialize()
    {
        parent::_initialize();
    }
    

    // 导入表格界面
	public function index()
	{
	    $map['uid'] = $_SESSION['think']['ADMIN_ID'];
        $host = DB::name('user_hosts')->where($map)->find();

        $list = array();
        if($host){
            $list = explode('|', $host['hosts']);
        }
        
        $this->assign('list',$list);
		return $this->fetch();
	}

    // 导入表格
    public function import(){
         if($this->request->isPost()){
            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');
            $host = intval($this->request->param('host'));

            $exceltype = intval($this->request->param('exceltype'));
            if(!empty($excel_name) && !empty($excel_url) && !empty($exceltype) && !empty($host)){
                $excelData = $this->excelRead($excel_url);       

                if ($excelData[0][1] != 'Description'  && $exceltype == 2) {
                    $this->error('请选择拒付表！');
                }

                if ($excelData[0][1] != 'Type'  &&  $exceltype == 1) {
                    $this->error('请选择余额表！');
                }



                // 数据入库

                array_shift($excelData);  //删除第一个数组(标题);  
                $logdata['host'] = $data['host'] = $host; 
                $logdata['type'] = $exceltype;
                $logdata['uid'] = $data['uid'] = $_SESSION['think']['ADMIN_ID'];
                //整合数据
                // 导入拒付数据
                if($exceltype == 2){

                    foreach ($excelData as $key => $value) {

                        
                        $map['id'] = $data['id'] = isset($value[0]) ? $value[0] : '';
                        $data['Description'] = isset($value[1]) ? $value[1] : '';
                        $data['Dispute_Created_UTC']  = isset($value[2]) ? $value[2] : '';
                        $data['Charge_Created_UTC']  = isset($value[3]) ? $value[3] : '';
                        $data['Dispute_Amount']  = isset($value[4]) ? $value[4] : '';
                        $data['Dispute_Currency']  = isset($value[5]) ? $value[5] : '';
                        $data['Charge_Amount']  = isset($value[6]) ? strtolower($value[6]) : '';
                        $data['Charge_Currency']  = isset($value[7]) ? $value[7] : '';
                        $data['Charge_ID'] = isset($value[8]) ? $value[8] : '';

                        
                        $logdata['logtype'] = 2; //新增拒付类型的日志

                        // 当前条目已经导入的话，则进行数据更新，否则新增条目
                        $check = Db::name('stripe_dispute')->where($map)->find();
                        if ($check) {
                            $data['updatetime'] = time();
                            $re = Db::name('stripe_dispute')->where($map)->update($data);
                        }else{
                            $data['addtime'] = time();
                            $data['updatetime'] = time();
                            $re = Db::name('stripe_dispute')->insert($data);
                            
                        }
                        
                    } 
                }

                // 导入余额数据
                if($exceltype == 1){
                    foreach ($excelData as $key => $value) {

                        $map['id'] = $data['id'] = isset($value[0]) ? $value[0] : '';
                        $data['Type'] = isset($value[1]) ? $value[1] : '';
                        $data['Source']  = isset($value[2]) ? $value[2] : '';
                        $data['Amount']  = isset($value[3]) ? $value[3] : '';
                        $data['Fee']  = isset($value[4]) ? $value[4] : '';
                        $data['Net']  = isset($value[5]) ? $value[5] : '';
                        $data['Currency']  = isset($value[6]) ? $value[6] : '';
                        $data['Created_UTC']  = isset($value[7]) ? strtolower($value[7]) : '';
                        $data['Available_On_UTC'] = isset($value[8]) ? $value[8] : '';
                        $data['Available_On_UTC_date'] = strtotime(date('Y-m-d', strtotime($value[8])));
                        $data['Created_UTC_datetime'] =  strtotime($value[7]);

                        $logdata['logtype'] = 1; //新增余额类型的日志

                        // 当前条目已经导入的话，则进行数据更新，否则新增条目
                        $check = Db::name('stripe_balance')->where($map)->find();
                        if ($check) {
                            $data['updatetime'] = time();
                            $re = Db::name('stripe_balance')->where($map)->update($data);
                        }else{
                            $data['addtime'] = time();
                            $data['updatetime'] = time();
                            $re = Db::name('stripe_balance')->insert($data);
                        }
                        
                    } 
                }

                $logdata['addtime'] = time();
                
                Db::name('stripe_logs')->insert($logdata);
                $this->success('导入成功' , url("stripe/index"));
            }else{
                $this->error('参数错误');
            }

         }
    }


    // 导入日志列表
    public function logs(){
        $map['uid'] = $_SESSION['think']['ADMIN_ID'];
        $list = Db::name('stripe_logs')
                ->where($map)
                ->order('id DESC')
                ->paginate(20, false, [
                    'query' => request()->param(),
                ]);

        $userlist = Db::name('user')->select();
        foreach ($userlist as $key => $value) {
            $user[$value['id']] = $value;
        }



        $page = $list->render();
        $this->assign("page", $page);
        $this->assign('list',$list);
        $this->assign('user',$user);
        return $this->fetch();
    }

    // 统计数据
    public function statistics(){
        if($this->request->isPost() && $this->request->param('host')  && $this->request->param('hk')>0){
            $huikuan_data['huikuan'] =  trim($this->request->param('hk'));
            $mapfind['host'] = $huikuan_data['host'] =  intval($this->request->param('host'));
            $mapfind['uid'] = $huikuan_data['uid'] =  $_SESSION['think']['ADMIN_ID'];
            $check = Db::name('user_hosts_huikuan')->where($mapfind)->find();
            if($check){
                Db::name('user_hosts_huikuan')->where($mapfind)->data($huikuan_data)->update();
            }else{
                Db::name('user_hosts_huikuan')->data($huikuan_data)->insert();
            }
            echo json_encode(array('status'=>1, 'msg'=>'已设置！'));
            exit;
        }




        $uid = $_SESSION['think']['ADMIN_ID'];
        // 当前用户的用户数据
        $userinfo = Db::name('user')
            ->alias('a')
            ->join('cmf_role_user r','a.id = r.user_id')
            ->where('a.id = '.$uid)
            ->find();

        

        $statistics = array();
        $userlist = Db::name('user')->select();
        foreach ($userlist as $key => $value) {
            $user[$value['id']] = $value;
        }

        if($userinfo['role_id'] == 7){
            // 超管用户的所有查看权限
            $users = Db::name('user')
                ->alias('a')
                ->join('cmf_role_user r','a.id = r.user_id')
                ->join('cmf_user_hosts h','a.id = h.uid')
                ->where('r.role_id = 6')
                ->select();            
            }else{
                // 普通管理用户的查看权限
                $users = Db::name('user')
                ->alias('a')
                ->join('cmf_role_user r','a.id = r.user_id')
                ->join('cmf_user_hosts h','a.id = h.uid')
                ->where('r.role_id = 6 and a.id='.$uid)
                ->select();     
            }



        // 总计统计
        // 总收款
        $total_statistics['Net'] = Db::name('stripe_balance')->sum('Net');
        $total_statistics['Fee'] = Db::name('stripe_balance')->sum('Fee');
        $total_statistics['shoukuan'] = $total_statistics['Net'] + $total_statistics['Fee'];


        // 冻结
        $total_statistics['dongjie'] = 0;
        // 总payOut
        $total_payout_maps['Type'] = 'payout';
        $total_payout_maps['Net'] = array('lt', 0);
        $total_statistics['payout'] = abs(Db::name('stripe_balance')->where($total_payout_maps)->sum('Net'));

        // 总调整数
        $total_tiaozheng_maps['Type'] = 'adjustment';
        $total_statistics['tiaozheng'] = abs(Db::name('stripe_balance')->where($total_tiaozheng_maps)->sum('Net'));


        // 总手续费
        $total_shouxu_maps['Type'] = 'charge';
        $total_statistics['shouxu'] = abs(Db::name('stripe_balance')->where($total_shouxu_maps)->sum('Fee'));


        //总charge
        $total_charge_maps['Type'] = 'charge';
        $total_statistics['charge'] = Db::name('stripe_balance')->where($total_charge_maps)->sum('Net');

        // 总增长
        $total_zengzhang_maps_today['Available_On_UTC_date'] = date('Y-m-d', time());
        $total_zengzhang_maps_today['Type'] = 'charge';
        $total_zengzhang_today = abs(Db::name('stripe_balance')->where($total_zengzhang_maps_today)->sum('Fee'));

        $total_zengzhang_maps_yestoday['Available_On_UTC_date'] = date('Y-m-d',strtotime(date('Y-m-d', time()))-1);
        $total_zengzhang_maps_yestoday['Type'] = 'charge';
        $total_zengzhang_yestoday = abs(Db::name('stripe_balance')->where($total_zengzhang_maps_yestoday)->sum('Fee'));
        $total_statistics['zengzhang'] = $total_zengzhang_today-$total_zengzhang_yestoday;

        // 回款率
        $total_statistics['huikuan'] = Db::name('user_hosts_huikuan')->sum('huikuan');

        //回款率

        if($total_statistics['shoukuan'] > 0){
            $total_statistics['huikuanlv'] = $total_statistics['huikuan']*100/$total_statistics['shoukuan'];
        }else{
            $total_statistics['huikuanlv'] = 0;
        }


        // 总计统计




        $arr = array();
        foreach ($users as $k => $v) {
            //$v['hosts'] 当前用户管理的主机信息
            if($v['hosts'])   {
                $host_arr = explode('|', $v['hosts']);  
                foreach ($host_arr as $kv => $vv) {
                    

                    $arr[$k]['userinfo'] = $v;
                    $arr[$k]['host'][$kv] = $vv;

                    // 收款 余额表中类型为charge的所有Net字段的总和+所有Fee字段的总和
                    $shoukuan_maps['host'] = $vv;
                    $shoukuan_maps['uid'] = $v['uid'];
                    $shoukuan_maps['Type'] = 'charge';
                    $statistics['Net'] = Db::name('stripe_balance')->where($shoukuan_maps)->sum('Net');
                    $statistics['Fee'] = Db::name('stripe_balance')->where($shoukuan_maps)->sum('Fee');
                    $statistics[$vv]['shoukuan'] = $statistics['Net'] + $statistics['Fee'];


                    // charge : 余额表中类型为charge的所有Net字段的总和
                    $charge_maps['host'] = $vv;
                    $charge_maps['uid'] = $v['uid'];
                    $charge_maps['Type'] = 'charge';
                    $statistics[$vv]['charge'] = Db::name('stripe_balance')->where($charge_maps)->sum('Net');


                    // payout : 余额表中类型为payout的所有负值Net字段的总和的绝对值
                    $payout_maps['host'] = $vv;
                    $payout_maps['uid'] = $v['uid'];
                    $payout_maps['Type'] = 'payout';
                    $payout_maps['Net'] = array('lt', 0);
                    $statistics[$vv]['payout'] = abs(Db::name('stripe_balance')->where($payout_maps)->sum('Net'));


                    // 回款
                    $huikuan_maps['host'] = $vv;
                    $huikuan_maps['uid'] = $v['uid'];
                    $huikuan = Db::name('user_hosts_huikuan')->where($huikuan_maps)->field('huikuan')->find();
                    $statistics[$vv]['huikuan'] = $huikuan['huikuan']?$huikuan['huikuan']:0;
   

                    // 冻结 
                    $dongjie_base_total = $this->tocheck($vv, $v['uid']);
                    
                    if(abs($dongjie_base_total['odd'])){
                        $dongjie_base_total_odd = abs($dongjie_base_total['odd']);
                    }else{
                        $dongjie_base_total_odd = 0;
                    }
                    
                    if(abs($dongjie_base_total['even'])){
                        $dongjie_base_total_even = abs($dongjie_base_total['even']);
                    }else{
                        $dongjie_base_total_even = 0;
                    }

                    // $dongjie_base_maps['host'] = $vv;
                    // $dongjie_base_maps['uid'] = $v['uid']; 
                    // $dongjie_base_maps['Type'] = 'reserved_funds'; 
                    // $dongjie_base_maps['Amount'] = array('gt', 0);
                    // $dongjie_total = Db::name('stripe_balance')->where($dongjie_base_maps)->order('Created_UTC_datetime asc')->sum('Amount');
                    // var_dump($dongjie_base_total);
                    // var_dump($dongjie_total);
                    $statistics[$vv]['dongjie'] = $dongjie_base_total_odd - $dongjie_base_total_even;



                    // // 拒付: 拒付表里是吗
                    $jufu_maps['host'] = $vv;
                    $jufu_maps['uid'] = $v['uid'];
                    $statistics[$vv]['jufu'] = Db::name('stripe_dispute')->where($jufu_maps)->sum('Charge_Amount');





                    // // 退款 : 余额表里的类型为refund的所有Net字段总和的绝对值
                    $tuikuan_maps['host'] = $vv;
                    $tuikuan_maps['uid'] = $v['uid'];
                    $tuikuan_maps['Net'] = array('lt', 0);
                    $tuikuan_maps['Type'] = 'refund';
                    $statistics[$vv]['tuikuan'] = abs(Db::name('stripe_balance')->where($tuikuan_maps)->sum('Net'));



                    // // 调整数
                    $tiaozheng_maps['host'] = $vv;
                    $tiaozheng_maps['uid'] = $v['uid'];
                    $tiaozheng_maps['Type'] = 'adjustment';
                    $statistics[$vv]['tiaozheng'] = abs(Db::name('stripe_balance')->where($tiaozheng_maps)->sum('Net'));



                    // // 手续费
                    $shouxu_maps['host'] = $vv;
                    $shouxu_maps['uid'] = $v['uid'];
                    $shouxu_maps['Type'] = 'charge';
                    $statistics[$vv]['shouxu'] = abs(Db::name('stripe_balance')->where($shouxu_maps)->sum('Fee'));




                    // // 收款天数
                    $days_maps['host'] = $vv;
                    $days_maps['uid'] = $v['uid'];
                    $days_maps['Type'] = 'charge';
                    $statistics[$vv]['days'] = count(Db::name('stripe_balance')->distinct(true)->where($days_maps)->field('Available_On_UTC_date')->select());



                    // 增长
                    $zengzhang_maps_today['Available_On_UTC_date'] = date('Y-m-d', time());
                    $zengzhang_maps_today['Type'] = 'charge';
                    $zengzhang_today = abs(Db::name('stripe_balance')->where($zengzhang_maps_today)->sum('Fee'));

                    $zengzhang_maps_yestoday['Available_On_UTC_date'] = date('Y-m-d',strtotime(date('Y-m-d', time()))-1);
                    $zengzhang_maps_yestoday['Type'] = 'charge';
                    $zengzhang_yestoday = abs(Db::name('stripe_balance')->where($zengzhang_maps_yestoday)->sum('Fee'));
                    $statistics[$vv]['zengzhang'] = $zengzhang_today-$zengzhang_yestoday;
                    




                    // 回款率 ，当前主机的汇款除以收款
                    if($statistics[$vv]['shoukuan']>0){
                        $statistics[$vv]['huikuanlv'] = $statistics[$vv]['huikuan']*100/$statistics[$vv]['shoukuan'];
                    }else{
                         $statistics[$vv]['huikuanlv'] = 0;
                    }




                    // 总回款
                    $total_huikuan_maps['uid'] = $v['uid'];
                    $statistics[$vv]['total_huikuan'] = Db::name('user_hosts_huikuan')->where($total_huikuan_maps)->sum('huikuan');


                    $total_shoukuan_maps['uid'] = $v['uid'];
                    $total_shoukuan_maps['Type'] = 'charge';
                    $total_shoukuan_Net = Db::name('stripe_balance')->where($total_shoukuan_maps)->sum('Net');
                    $statisttotal_shoukuan_Fee = Db::name('stripe_balance')->where($total_shoukuan_maps)->sum('Fee');
                    $statistics[$vv]['total_shoukuan'] = $total_shoukuan_Net + $statisttotal_shoukuan_Fee;


                    // 总回款率 ，当前站长的总回款除以总收款
                    if($statistics[$vv]['total_shoukuan']>0){
                        $statistics[$vv]['zonghuikuanlv'] = $statistics[$vv]['total_huikuan']*100/$statistics[$vv]['total_shoukuan'];
                    }else{
                         $statistics[$vv]['zonghuikuanlv'] = 0;
                    }






                    // 预计
                   // $statistics[$vv]['yuji'] = 0;
                }
            }

        }


        // var_dump($userinfo);
        // exit;

        $this->assign('user',$user);
        $this->assign('userinfo',$userinfo);
        $this->assign('arr',$arr);
        $this->assign('statistics',$statistics);
        $this->assign('total_statistics',$total_statistics);
        return $this->fetch();
    }




    // 冻结数据算法
    public function tocheck($host, $uid){
        $maps['host'] = $host;
        $maps['uid'] = $uid;
        $maps['Type'] = 'reserved_funds';
        $rss = Db::name('stripe_balance')->where($maps)->field('id,Amount')->order('Created_UTC_datetime asc')->select(); 
        $total['odd'] = 0; //奇数
        $total['even'] = 0;
        foreach ($rss as $k => $v) {
            if($k%2 == 1){
                // 奇数
                if($v['Amount'] < 0 ){
                    $total['odd'] +=  $v['Amount'];
                }
            }else{
                // 偶数
                if($v['Amount'] < 0 ){
                    $total['even'] +=  $v['Amount'];
                }                
            }

        }
        return $total;
    }

    // 删除日志及当天导入的数据
    public function delete(){
        $map['id']  = $this->request->param('id', 0, 'intval');
        // 检索当前日志的信息
        $delinfo = Db::name('stripe_logs')->where($map)->find();
        if (empty($delinfo)) {
            $this->error('该日志信息不存在!');
        }
        if($delinfo['type'] == 2){
            $logdata['logtype'] = 4; //删除拒付类型的日志
            $logdata['type'] = 2;
            $_db = Db::name('stripe_dispute');
        }else{
            $logdata['logtype'] = 3; //删除余额类型的日志
            $_db = Db::name('stripe_balance');
            $logdata['type'] = 1;
        }
        
        $today_start = strtotime( date('Y-m-d 00:00:00', $delinfo['addtime']));
        $today_end = strtotime( date('Y-m-d 00:00:00', $delinfo['addtime'])) + 24*3600;  

        $logdata['host'] = $delmap['host'] = $delinfo['host'];
        $delmap['addtime'] = array('between',[$today_start,$today_end]);
        $rs = $_db->where($delmap)->delete();
        $logdata['addtime'] = time();
        $logdata['uid'] = $_SESSION['think']['ADMIN_ID'];
        Db::name('stripe_logs')->insert($logdata);
        $this->success("删除成功！", url("stripe/logs"));

    }    




    // 添加虚拟机
    public function addhost(){
        if($this->request->isPost()){
           if (!empty($_POST['host'])) {
                $map['uid'] = $data['uid'] = $_SESSION['think']['ADMIN_ID'];
                $data['hosts'] = trim($_POST['host']);
                $rs = DB::name('user_hosts')->where($map)->find();
                if($rs){
                     $result = DB::name('user_hosts')->where($map)->update($data);
                }else{
                    $result = DB::name('user_hosts')->data($data)->insert();
                }


                if ($result !== false) {
                    $this->success("修改成功！", url("stripe/addhost"));
                } else {
                    $this->error("修改失败！", url("stripe/addhost"));
                }
            } else {
                $this->error("参数错误");
            }
        }else{
            $map['uid'] = $_SESSION['think']['ADMIN_ID'];
            $host = DB::name('user_hosts')->where($map)->find();
            $this->assign('host',$host);
            return $this->fetch();
        }

    }

    /**
     *读取excel数据
     *string $excel_url  excel路径
     *return array $excel_array 读取数组
     */
    public function excelRead($excel_url)
    {
        Loader::import('PHPExcel.Classes.PHPExcel');
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
        $filename = ROOT_PATH.'public/upload/'.$excel_url;  //文件路径
        $extension = cmf_get_file_extension($excel_url);    //文件扩展名

        if($extension == 'xlsx'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else if($extension == 'csv'){
            
            $objReader = \PHPExcel_IOFactory::createReader('CSV')
                ->setDelimiter(',')  
                // ->setInputEncoding('GBK') //不设置将导致中文列内容返回boolean(false)或乱码   
                ->setEnclosure('"')  
                // ->setLineEnding("\r\n")  //新版本可删除 
                ->setSheetIndex(0); 
                $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
                
                // $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
                // // array_shift($excel_array);  //删除第一个数组(标题);
                // $excelArray = trimExcelStrBlank($excel_array);
                // var_dump($excelArray);
                // exit();
        }else{
            $this->error('请上传Excel格式的文件！','');
        }

        $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
        // array_shift($excel_array);  //删除第一个数组(标题);
        $excelArray = trimExcelStrBlank($excel_array);
        return TrimArray($excelArray);
        // return $excel_array;
    }

}